package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.PicChangeHistoryDto;
import com.tianqu.tms.dto.TransferProcessHistoryDto;

/**
 * 
 * 历史表daoimp
 * 
 * 
 * @author tianshifu
 * 
 */
public class TransferProcessHistoryDaoImp extends TmsDao implements
		TransferProcessHistoryDao {

	protected TransferProcessHistoryDaoImp() throws TmsDaoException {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * 评估向历史表插一条数据
	 * 
	 * 车辆编号 业务类型2 业务受理人 业务受理时间
	 */
	@Override
	public void insertHisInfoAtAssess(TransferProcessHistoryDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			StringBuffer sql = new StringBuffer("INSERT INTO  ");
			sql.append(TmsTableNames.TMS_TRANSFER_PROCESS_HISTORY);
			sql.append(" (  ");
			sql.append(" SERIAL,  ");
			sql.append(" BUSINESSTYPE,  ");
			sql.append(" INSERTUSERID,  ");
			sql.append(" INSERTTIME,  ");
			sql.append(" REASON  ");
			sql.append(" )  ");
			sql.append(" VALUES  ");
			sql.append(" (?,?,?,SYSDATE(),? ) ");
			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, dto.getSerial());
			ps.setInt(2, dto.getBusinessType());
			ps.setInt(3, dto.getInsertUserId());
			ps.setString(4, dto.getReason());
			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}

	}

	/**
	 * 根据serial 载入历史记录
	 * */
	@Override
	public List<TransferProcessHistoryDto> loadTransferProcessHistoryBySerial(
			String serial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<TransferProcessHistoryDto> tHistoryList = new ArrayList<TransferProcessHistoryDto>();
		try {
			StringBuffer sql = new StringBuffer("SELECT ");
			sql.append("SERIAL,");
			sql.append("BUSINESSTYPE,");
			sql.append("REASON,");
			sql.append("INSERTUSERID,");
			sql.append("INSERTTIME ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_PROCESS_HISTORY);
			sql.append(" WHERE ");
			sql.append("SERIAL=?");
			sql.append(" ORDER BY INSERTTIME DESC");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);
			rs = ps.executeQuery();
			int i = 1;
			while (rs.next()) {
				TransferProcessHistoryDto dto = new TransferProcessHistoryDto();
				dto.setSerial(rs.getString("SERIAL"));
				dto.setBusinessType(rs.getInt("BUSINESSTYPE"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				dto.setReason(rs.getString("REASON"));
				if (i % 2 == 0) {
					dto.setCssFlag(1);
				} else {
					dto.setCssFlag(2);
				}
				dto.setNum(i);
				tHistoryList.add(dto);
				i++;
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}

		return tHistoryList;
	}

	@Override
	public void deleteHisInfoBySerial(String serial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("");
			sql.append("DELETE ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_PROCESS_HISTORY);
			sql.append(" WHERE ");
			sql.append(" SERIAL='"+serial+"' ");
			ps = conn.prepareStatement(sql.toString());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}	
	}

	@Override
	public TransferProcessHistoryDto findCheckFile(String serial, int businessType)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TransferProcessHistoryDto dto = new TransferProcessHistoryDto();
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("SERIAL,");
		sql.append("INSERTTIME ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_TRANSFER_PROCESS_HISTORY);
		sql.append(" WHERE ");
		sql.append("SERIAL=?");
		sql.append(" AND BUSINESSTYPE=?");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);
			ps.setInt(2, businessType);
			rs=ps.executeQuery();
			while(rs.next()){
				dto.setSerial(rs.getString("SERIAL"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return dto;
	}

	@Override
	public void insertPicChangeHistory(PicChangeHistoryDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO  ");
			sql.append(TmsTableNames.TMS_PAD_HISTORY);
			sql.append(" (  ");
			sql.append(" LICENSEPLATE,  ");
			sql.append(" SUCCESSFLAG,  ");
			sql.append(" OPERATEFLAG,  ");
			sql.append(" REMARK,  ");
			sql.append(" INSERTTIME,  ");
			sql.append(" INSERTUSERID, ");
			sql.append(" USERNAME ");
			sql.append(" )  ");
			sql.append(" VALUES  ");
			sql.append(" (?,?,?,?,SYSDATE(),?,? ) ");
			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, dto.getLicensePlate());
			ps.setInt(2, dto.getSuccessFlag());
			ps.setInt(3, dto.getOperateFlag());
			ps.setString(4, dto.getRemark());
			ps.setString(5, dto.getInsertUserId());
			ps.setString(6, dto.getUserName());
			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
		
		
	}
	
	
	@Override
	public void insertHisInfoBySerial(TransferProcessHistoryDto dto)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			StringBuffer sql = new StringBuffer("INSERT INTO  ");
			sql.append(TmsTableNames.TMS_TRANSFER_PROCESS_HISTORY);
			sql.append(" (  ");
			sql.append(" SERIAL,  ");
			sql.append(" BUSINESSTYPE,  ");
			sql.append(" INSERTUSERID,  ");
			sql.append(" REASON,  ");
			sql.append(" INSERTTIME  ");
			sql.append(" )  ");
			sql.append(" VALUES  ");
			sql.append(" (?,?,?,?,SYSDATE()) ");
			ps = conn.prepareStatement(sql.toString());

			ps.setString(1, dto.getSerial());
			ps.setInt(2, dto.getBusinessType());
			ps.setInt(3, dto.getInsertUserId());
			ps.setString(4, dto.getReason());
			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}

	}
	/**
	 * 根据serial 载入历史记录
	 * */
	@Override
	public List<TransferProcessHistoryDto> loadTransferProcessHistoryBySerialInsertTime(
			String serial,String insertTime) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<TransferProcessHistoryDto> tHistoryList = new ArrayList<TransferProcessHistoryDto>();
		try {
			StringBuffer sql = new StringBuffer("SELECT ");
			sql.append("SERIAL,");
			sql.append("BUSINESSTYPE,");
			sql.append("REASON,");
			sql.append("INSERTUSERID,");
			sql.append("INSERTTIME ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_TRANSFER_PROCESS_HISTORY);
			sql.append(" WHERE ");
			sql.append("SERIAL=?");
			sql.append(" AND DATE_FORMAT(INSERTTIME,'%Y-%m-%d')='"+ insertTime +"'");
			sql.append(" ORDER BY INSERTTIME DESC");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);
			rs = ps.executeQuery();
			int i = 1;
			while (rs.next()) {
				TransferProcessHistoryDto dto = new TransferProcessHistoryDto();
				dto.setSerial(rs.getString("SERIAL"));
				dto.setBusinessType(rs.getInt("BUSINESSTYPE"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				dto.setReason(rs.getString("REASON"));
				if (i % 2 == 0) {
					dto.setCssFlag(1);
				} else {
					dto.setCssFlag(2);
				}
				dto.setNum(i);
				tHistoryList.add(dto);
				i++;
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
		return tHistoryList;
	}
}
